package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.dataentry.InTransitLots;
import com.scalar.chiptrack.operations.dataentry.TransitLotNumber;
import com.scalar.chiptrack.utils.SqlQueryMappings;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

import org.apache.struts.util.LabelValueBean;

/**
 * Created by Sudhakar.
 * Date: Sep 5, 2005
 * Time: 9:17:24 PM
 */
public class TransitDataEntryDAO
{

    /**
     *
     * @return
     * @throws com.scalar.ScalarException
     */
    public static ArrayList loadAllTransitLots( String processStep_id ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList transitLotList = new ArrayList();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select * from fn_load_all_transit_lots( ? ) " );
            pstmt.setString( 1, processStep_id );
            rset = pstmt.executeQuery();
            InTransitLots inTransitLots = null;

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setShippedLotID( rset.getString( "ShippedLot_Id" ) );
                transitLotList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all Lotnumbers from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return transitLotList;
    }


    /**
     *
     * @return
     * @throws com.scalar.ScalarException
     */
    public static InTransitLots loadAllTransitLotInfo( String shippedLot_id ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        InTransitLots inTransitLots = new InTransitLots();

        int shippedLot = -1;

        try
        {
            shippedLot = Integer.parseInt( shippedLot_id );
        }
        catch(NumberFormatException npe)
        {
           shippedLot = -1;
        }

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT Lt.Lot_number, SL.SHIP_DATE, SL.SHIP_QTY, TLT.TLT_CUSTOMER_PO , TLT.TLT_CUSTOMER_SO, TLT.TLT_INVOICE_NUMBER , TLT.TLT_NEXTLOCATION, CAST(TLT.TLT_ETA AS DATETIME) AS TLT_ETA, CAST(TLT.TLT_ETD AS DATETIME) AS TLT_ETD, TLT.WAFER_ID, LT.LOT_COMMENTS FROM LOT_TABLE LT, SHIPPED_LOT SL, TRANSIT_LOT_TABLE TLT WHERE LT.LOT_ID = SL.LOT_ID AND SL.SHIPPEDLOT_ID = TLT.SHIPPEDLOT_ID AND SL.SHIPPEDLOT_ID = ? " );
            pstmt.setInt( 1, shippedLot );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                inTransitLots.setLotNumber( rset.getString( "Lot_number" ) );
                inTransitLots.setShipDate( rset.getString( "SHIP_DATE" ) );
                inTransitLots.setShipQty( rset.getString( "SHIP_QTY" ) );
                inTransitLots.setCustomerPO( rset.getString( "TLT_CUSTOMER_PO" ) );
                inTransitLots.setCustomerSO( rset.getString( "TLT_CUSTOMER_SO" ) );
                inTransitLots.setInvoiceNo( rset.getString( "TLT_INVOICE_NUMBER" ) );
                inTransitLots.setNextLocation( rset.getString( "TLT_NEXTLOCATION" ) );
                inTransitLots.setEta( rset.getString( "TLT_ETA" ) );
                inTransitLots.setEtd( rset.getString( "TLT_ETD" ) );
                inTransitLots.setWaferId( rset.getString( "WAFER_ID" ) );
                inTransitLots.setComments( rset.getString( "LOT_COMMENTS" ) );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Transit Lot Information from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return inTransitLots;
    }

    public static void saveTransitLot( DBTransaction dbTransaction, InTransitLots transitLotInfo ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            // Get the connection from regular datasource if its not in a manual transaction
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( " { call sp_create_update_data_entry_shipped_lot(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }" );

            if ( transitLotInfo != null )
            {

                pstmt.setString( 1, transitLotInfo.getLotNumber() );
                pstmt.setString( 2, transitLotInfo.getProcessStepId() );
                pstmt.setString( 3, transitLotInfo.getCustomerPO() );
                pstmt.setString( 4, transitLotInfo.getCustomerSO() );
                pstmt.setString( 5, transitLotInfo.getInvoiceNo() );
                pstmt.setString( 6, transitLotInfo.getProcessType() );
                pstmt.setString( 7, transitLotInfo.getNextLocation() );
                pstmt.setString( 8, transitLotInfo.getShipDate() );
                pstmt.setString( 9, transitLotInfo.getShipQty() );
                pstmt.setString( 10, transitLotInfo.getEta() );
                pstmt.setString( 11, transitLotInfo.getEtd() );
                pstmt.setString( 12, transitLotInfo.getLocationId() );
                pstmt.setString( 13, transitLotInfo.getWaferId() );
                pstmt.setString( 14, transitLotInfo.getShippedLotID() );
                pstmt.setString( 15, transitLotInfo.getComments() );

                pstmt.executeUpdate();
            }

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not save Shipping Lot Information into database", sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if ( dbTransaction == null )
            {
                try
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

     /** To get the location names having the process type as Class Store
     *
     * @return ArrayList
     * @throws com.scalar.ScalarException
     */
    public static ArrayList loadClassStroeLocations( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection        conn      = null;
	    PreparedStatement pstmt     = null;
	    ResultSet         rs        = null;
        ArrayList         locations = null;

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.vendorLocation.ClassStoreLocations") );
            rs = pstmt.executeQuery();

            locations = new ArrayList();

            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("LOCATION_NAME"), rs.getString("LOCATION_NAME")+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not load Class Store locations", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return locations;
    }

    /**
     *
     * @return
     * @throws com.scalar.ScalarException
     */
    public static int checkBlankFGILot( String lotNumber, String procssStepID ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;

        int blankFGICount = -1;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT COUNT(*) AS BLANFGI_COUNT FROM LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS WHERE LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_NUMBER = ? AND P.PROCESSSTEP_ID = ( SELECT NEXT_PROCESSSTEP_ID FROM PROCESS_STEP_TRAVERSE_TABLE WHERE PROCESSSTEP_ID = ? ) " );
            pstmt.setString( 1, lotNumber );
            pstmt.setString( 2, procssStepID );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                blankFGICount = rset.getInt("BLANFGI_COUNT");
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load Blank FGI Lot from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return blankFGICount;
    }

    /**
     *
     * @param processStepId
     * @param locationId
     * @param processType
     * @return
     * @throws ScalarException
     */
    public static ArrayList getTestLotNumbers ( String processStepId , String locationId, String processType ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset  = null;
        ArrayList lotNumbers    = new ArrayList();
        TransitLotNumber transitLotNumber   = null;
        DBTransaction dbTransaction = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            cstmt   = conn.prepareCall( "SELECT LT.LOT_NUMBER, L.L_VENDOR_NAME, FTL.FTL_QTY_IN FROM LOT_TABLE LT, PROCESS_STEP PS, FINAL_TEST_LOT FTL, LOCATION L WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = FTL.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.PROCESSSTEP_ID=? AND LT.LOT_CURRENT_STATUS='Running' AND PS.PS_TYPE_NAME LIKE ? AND L.LOCATION_ID = ? ORDER BY LT.LOT_NUMBER " );
            cstmt.setString ( 1, processStepId );
            cstmt.setString ( 2, processType);
            cstmt.setString ( 3, locationId );
            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                transitLotNumber = new TransitLotNumber();
                transitLotNumber.setLotNumber( rset.getString ( "LOT_NUMBER" ) );
                transitLotNumber.setNextLocation( rset.getString ( "L_VENDOR_NAME" ) );
                transitLotNumber.setShipQty( rset.getInt ( "FTL_QTY_IN" ) );
                lotNumbers.add ( transitLotNumber );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could Not Load Test Lot Numbers", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( cstmt != null )
            {
                try { cstmt.close(); } catch( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return lotNumbers;
    }

    /**
     *
     * @param processStepId
     * @param locationId
     * @param processType
     * @return
     * @throws ScalarException
     */
    public static ArrayList getFabLotNumbers(  String processStepId , String locationId, String processType ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        ArrayList lotNumbers = new ArrayList();
        TransitLotNumber transitLotNumber = null;
        DBTransaction dbTransaction = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            cstmt = conn.prepareCall( " SELECT LT.LOT_NUMBER, L.L_VENDOR_NAME, FL.FL_INPUT_QUANTITY, FL.Wafer_id FROM LOT_TABLE LT, PROCESS_STEP PS, FAB_LOT FL, LOCATION L WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.LOT_ID = FL.LOT_ID AND LT.LOCATION_ID = L.LOCATION_ID AND LT.PROCESSSTEP_ID=? AND LT.LOT_CURRENT_STATUS = 'Running' AND PS.PS_TYPE_NAME LIKE ? AND L.LOCATION_ID=? ORDER BY LT.LOT_NUMBER " );
            cstmt.setString( 1, processStepId );
            cstmt.setString( 2, processType );
            cstmt.setString( 3, locationId );
            rset = cstmt.executeQuery();

            while ( rset.next() )
            {
                transitLotNumber = new TransitLotNumber();
                transitLotNumber.setLotNumber( rset.getString( "LOT_NUMBER" ) );
                transitLotNumber.setNextLocation( rset.getString( "L_VENDOR_NAME" ) );
                transitLotNumber.setShipQty( rset.getInt( "FL_INPUT_QUANTITY" ) );
                transitLotNumber.setWaferId( rset.getString( "Wafer_id" ) );
                lotNumbers.add( transitLotNumber );
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
            throw new ScalarException( "Could Not Load Fab Lot Numbers", e );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lotNumbers;
    }

}
